# -*- coding:utf-8 -*-
the_day = '20210820'  #截至计算时间

# HtmlToEmail
# 生成HTML表格和email发送，带CSV附件

import copy
from datetime import datetime,timedelta
import smtplib
from email.mime.text import MIMEText
from email.utils import formataddr
from email.mime.base import MIMEBase
from email import encoders
from stock_py.SysFile import Base_File_Oper
from email.mime.multipart import MIMEMultipart
# from email.mime.application import MIMEApplication

class htmlViewTool():

    # rel_path = os.path.dirname(os.path.dirname(__file__)) + '/ConfigFileInput/'
    @staticmethod
    def build_html_view(head,titles,dataF,filename):

        # 常规表格
        # head, titles, data = get_example_data(dataF)
        html1 = data_to_html(dataF, [titles], head)
        # print(html1)
        filename_csv = filename + ".csv"
        mail(html1, '61992850@qq.com',filename_csv)
        # mail(html1, '516019740@qq.com',filename_csv)

    @staticmethod
    def test111(): #邮件测试
        to_address = '61992850@qq.com'  # 收件箱
        subject = '邮件主题'
        content = "内容"
        filename = "23-01-13-全部的rps.csv"
        password = 'aqvxjjfxfwmjbhhf'  # "授权码"
        from_address = '61992850@qq.com'  # 发件箱
        # auto_send_email(to_address, subject, content, password, from_address)
        mail(content, '61992850@qq.com',filename) #张坤邮箱
        mail(content, '516019740@qq.com',filename) #王素珍邮箱
        mail(content, '17610093388@163.com', filename)  # 邮箱


#=========================================发邮件相关=======================================
# 看下下面这个地址是教程，需要把修改邮箱授权码和发件人的邮箱账号，我是自己给自己发的，收件人也是我
# https://blog.csdn.net/weixin_44688529/article/details/125762145
def mail(content,resever,filename):
    my_sender = '61992850@qq.com'  # 发件人邮箱账号
    my_pass = 'aqvxjjfxfwmjbhhf'  # 发件人邮箱授权码
    # my_user = '61992850@qq.com'  # 收件人邮箱账号，我这边发送给自己
    my_user = resever  # 收件人邮箱账号，我这边发送给自己
    ret = True
    try:
        # content = "ssdajdjsajjdaj"
        msg = MIMEMultipart()  # 可变的才能添加附件
        tips1 = "CSV附件表格是全部的可以用Excel打开自己筛选，基本跟杰哥数据一样，基础配置是已选的：rps3 ∪ rps5 ∪ rps20取了并集，按一二级行业排序，行业最高rps大于95的才展示\n"
        tips2 = "基础配置：rps3，rps5大于85，流通市值是40亿，可筛选50亿的\n"
        tips3 = "可选配置：收盘价接近120日的最高价的95%，1表示已经是最高，可选配置：rps20=Y表示大于85，Y50表示流通50亿，换手率等\n"
        tips4 = "筛选的表格是rps3和rps5交集并且满足收盘价接近120日的最高价的95%: rps3 ∩ rps5 ∩ (接近新高)\n"

        tips5 = "生成时间：" + datetime.now().strftime("%y-%m-%d %H:%M:%S") + '\n '
        strd_temp = tips1 + tips2 + tips3 + tips4 + tips5
        msgTextTip = MIMEText(strd_temp, 'plain', 'utf-8') #说明
        msg.attach(msgTextTip)

        # msgText = MIMEText(timeStr, 'plain', 'utf-8')
        msgText = MIMEText(content, 'html', 'utf-8') #HTML表格
        msg.attach(msgText)

        # # 构造附件1，txt文件========#CSV附件==================================
        # filename = "23-01-13-全部的rps.csv"
        fileToSend = Base_File_Oper.rel_path + "csvDataOutput/" + filename
        fp = open(fileToSend, "rb")
        attachment = MIMEBase('application', 'csv')
        # part = MIMEApplication(open(fileToSend, 'rb').read())
        attachment.set_payload(fp.read())
        attachment.add_header('Content-Disposition', 'attachment', filename=filename)
        fp.close()
        encoders.encode_base64(attachment)
        msg.attach(attachment)
        # 构造附件1，txt文件==========================================

        msg['From'] = formataddr([filename, my_sender])  # 括号里的对应发件人邮箱昵称、发件人邮箱账号
        msg['To'] = formataddr(["用户", my_user])  # 括号里的对应收件人邮箱昵称、收件人邮箱账号
        msg['Subject'] = "今日RPS报告"  # 邮件的主题，也可以说是标题

        server = smtplib.SMTP_SSL("smtp.qq.com", 465)  # 发件人邮箱中的SMTP服务器，端口是25
        server.login(my_sender, my_pass)  # 括号中对应的是发件人邮箱账号、邮箱授权码
        server.sendmail(my_sender, [my_user, ], msg.as_string())  # 括号中对应的是发件人邮箱账号、收件人邮箱账号、发送邮件
        server.quit()  # 关闭连接
        print("邮件发送成功")
    except Exception:  # 如果 try 中的语句没有执行，则会执行下面的 ret=False
        print("邮件xxxx失败")
        ret = False
    return ret




def auto_send_email(to_address, subject, content, password, from_address='your_email_address@foxmail.com'):
    """
    :param to_address: 收件箱地址
    :param subject: 邮件主题
    :param content: 邮件内容
    :param from_address: 发件箱地址
    :param password: 授权码,需要在qq邮箱中设置获取 设置教程http://service.mail.qq.com/cgi-bin/help?subtype=1&&no=1001256&&id=28
    :param server_address: 服务器地址 smtplib.SMTP_SSL("smtp.qq.com", 465) 端口25，如果是设置SMTP的SSL加密方式，则SMTP服务器端口为465或587。
    :return:
    使用qq邮箱发送邮件的程序。一般用于报错提醒，需要去qq邮箱中开通密码
    """
    max_try_num = 1
    try_num = 0

    while True:
        try:
            # 创建一纯文字的实例
            msg = MIMEText(datetime.now().strftime("%m-%d %H:%M:%S") + '\n ' + content)
            msg["Subject"] = subject + ' ' + datetime.now().strftime("%m-%d %H:%M:%S")
            msg["From"] = from_address
            msg["To"] = ';'.join(to_address) # 接收字符串

            username = from_address
            server = smtplib.SMTP("smtp.qq.com", 465) # SMTP协议默认端口是25
            server.starttls()
            server.login(username, password)
            server.sendmail(from_address, to_address, msg.as_string())
            server.quit()
            print('邮件发送成功')
            break

        except :
            print('邮件发送失败')
            try_num += 1
            if try_num > max_try_num:
                break



#=========================================html 画表格相关=======================================
def get_sort_list(l_type):
    if l_type == '年级':
        return ['小学', '初中', '高中']
    if l_type == '学科':
        return ['语文', '数学', '英语', '物理', '化学']


def sort_data(data, sortlist):
    def get_sort_num(sort_dict, item):
        for k in sort_dict:
            if k in item:
                return sort_dict[k]
        return len(sort_dict)

    for i in range(len(sortlist) - 1, -1, -1):
        sort_mode = sortlist[i]
        sort_dict = {}
        sort_len = len(sort_mode)
        for j in range(sort_len):
            sort_dict[sort_mode[j]] = j
        data = sorted(data, key=lambda x: get_sort_num(sort_dict, x[i]))
    return data


def format_data(data_c):
    data = copy.deepcopy(data_c)
    for i in range(len(data)):
        for j in range(len(data[i])):
            data[i][j] = [data[i][j], 1, 1, '', '']
    return data


def rowspan_data(data, datacol):
    datacol = min(len(data), datacol)
    last_split = [0, len(data)]  # 前一列的分片，后一列分片要在前一列分片的基础上再分
    for j in range(datacol):  # 遍历列
        next_split = [0]
        for spliti in range(len(last_split) - 1):  # 遍历分片
            item_num = {}  # 值和出现次数
            item_list = []  # 值出现顺序，去重
            for i in range(last_split[spliti], last_split[spliti + 1]):  # 遍历分片下的单元格
                item = data[i][j][0]  # 单元格的值
                if item not in item_num:
                    item_list.append(item)
                    item_num[item] = 0
                item_num[item] = item_num[item] + 1  # 统计出现次数
            item_in = set()  # 合并单元格rowspan除了第一个外都写0
            for i in range(last_split[spliti], last_split[spliti + 1]):  # 遍历分片下的单元格
                item = data[i][j][0]  # 单元格的值
                if item not in item_in:
                    data[i][j][1] = item_num[item]  # 把出现次数写进去
                    item_in.add(item)
                else:
                    data[i][j][1] = 0

            num_list = []  # 分片内值和次数
            for item in item_list:
                num_list.append(item_num[item])
            last_num = next_split[-1]
            for num in num_list:
                next_split.append(last_num + num)
                last_num = next_split[-1]
        last_split = next_split  # 生成新的分片
    return data


def colspan_data(data, datacol):
    datacol = min(len(data[0]), datacol)
    for i in range(len(data)):
        item_num = {}  # 值和出现次数
        item_list = []  # 值出现顺序，去重
        for j in range(datacol):
            item = data[i][j][0]
            if item not in item_num:
                item_list.append(item)
                item_num[item] = 0
            item_num[item] = item_num[item] + 1  # 统计出现次数
        item_in = set()  # 合并单元格rowspan除了第一个外都写0
        for j in range(datacol):
            item = data[i][j][0]  # 单元格的值
            if item not in item_in:
                data[i][j][2] = item_num[item]  # 把出现次数写进去
                item_in.add(item)
            else:
                data[i][j][2] = 0
    return data


def font_data(data, args):
    '''
    method:可根据需求对此函数进行修改
    '''
    colors = ["#00BB00", "#FF8000", "#FF0000", '#FF00FF']  # 绿、橙、红、紫
    sizes = ['2', '3', '4']
    for i in range(len(data)):
        for j in range(len(data[i])):
            data[i][j][3] = colors[i % len(colors)]
            data[i][j][4] = sizes[j % len(sizes)]
    return data


def get_sub_html(h_type, args=[]):
    colorcode = {'gray': '#ECEDF2;'}
    if h_type == 'table0':
        html = "<br><table border='1' cellspacing='0' cellpadding='0' style=\" border: 1px solid #CECFD4; border-collapse: collapse; font-size: 12px; " + \
               "font-family: 'Helvetica Neue',Helvetica,'PingFang SC','Hiragino Sans GB','Microsoft YaHei','微软雅黑',Arial,sans-serif;\">\n"
    elif h_type == 'table1':
        html = "</table><br>\n"

    elif h_type == 'tr0':
        back_color = args[0] if len(args) > 0 else ''
        back_color = colorcode.get(str(back_color), '')
        html = '<tr style="text-align: center; vertical-align: middle; height: 30px; background-color: %s">\n' % (
            back_color)
    elif h_type == 'tr1':
        html = '</tr>\n'

    elif h_type == 'th0':
        rowspan = args[0] if len(args) > 0 else 1
        colspan = args[1] if len(args) > 1 else 1
        width = args[2] if len(args) > 2 else 80
        width = 80 if width == 0 else width
        html = '<th colspan="%s" rowspan="%s" style="border: 1px solid #CECFD4; width:%spx;" align="center">' % (
            colspan, rowspan, width)
    elif h_type == 'th1':
        html = '</th>\n'

    elif h_type == 'td0':
        rowspan = args[0] if len(args) > 0 else 1
        colspan = args[1] if len(args) > 1 else 1
        width = args[2] if len(args) > 2 else 80
        width = 80 if width == 0 else width
        html = '<td colspan="%s" rowspan="%s" style="border: 1px solid #CECFD4; width:%spx;" align="center">' % (
            colspan, rowspan, width)
    elif h_type == 'td1':
        html = '</td>\n'

    elif h_type == 'font0':
        color = args[0] if len(args) > 1 else ''
        size = args[1] if len(args) > 1 else ''
        html = '<font color="%s" size="%s">' % (color, size)
    elif h_type == 'font1':
        html = '</font>\n'

    else:
        html = ''
    return html


def get_html_data(data, titles, head, widths=[]):
    html = get_sub_html('table0')
    if head is not None:
        html += get_sub_html('tr0', ['gray']) + \
                get_sub_html('th0', [1, len(titles[0])]) + str(head) + get_sub_html('th1') + \
                get_sub_html('tr1')

    for row in titles:
        html += get_sub_html('tr0', ['gray'])
        for i in range(len(row)):
            cell, rowspan, colspan, color, size = row[i]
            width = widths[i] if len(widths) > i else 80
            if rowspan > 0 and colspan > 0:
                html += get_sub_html('th0', [rowspan, colspan, width]) + get_sub_html('font0', [color, size]) + str(
                    cell) + get_sub_html('font1') + get_sub_html('th1')

        #                 html += get_sub_html('th0', [rowspan, colspan, width]) + str(cell) + get_sub_html('th1')
        html += get_sub_html('tr1')

    data_colors = ['gray', 'None']
    color_i = 0
    for row in data:
        if row[0][1] > 0 and row[0][2] > 0:  # 换一种颜色
            color_i += 1
        this_color = data_colors[color_i % len(data_colors)]
        html += get_sub_html('tr0', [this_color])
        for i in range(len(row)):
            cell, rowspan, colspan, color, size = row[i]
            width = widths[i] if len(widths) > i else 80
            if rowspan > 0 and colspan > 0:
                html += get_sub_html('td0', [rowspan, colspan, width]) + get_sub_html('font0', [color, size]) + str(
                    cell) + get_sub_html('font1') + get_sub_html('td1')

        #                 html += get_sub_html('td0', [rowspan, colspan, width]) + str(cell) + get_sub_html('td1')
        html += get_sub_html('tr1')

    html += get_sub_html('table1')
    return html


def data_to_html(data, titles, head=None, datacol=0, rowspan=False, colspan=False, widths=[], font=False):
    '''
    data:数据
        type:二维数组
    titles:表头
        type:二维数组
        如果只有一行表头，可以写成[['表头1','表头2']]
    head:表格标题
        type:string
        默认为None，没有标题。
    datacol:数据起始列
        type:int
        数据列不会排序&合并，例如datacol=3，则除了前三列往后都是数据，合并时也不会进行合并
    rowspan:是否垂直合并
        value:True or False
        合并前请先排序，调用sort_data方法即可
    colspan:是否水平合并
        value:True or False
    widths:每列宽度
        type:一维数组
        数组的每个值分别代表了每一列的宽度，默认80，写0也是80
    font:是否需要自定义字号颜色
        value:True or False
        为True时表示，需要根据需求改变字号和颜色，需要修改font_data方法
    '''
    titles = format_data(titles)
    data = format_data(data)
    if rowspan == True and datacol > 0:  # 垂直合并
        titles = rowspan_data(titles, len(titles[0]))
        data = rowspan_data(data, datacol)
    if colspan == True and datacol > 0:  # 水平合并
        titles = colspan_data(titles, len(titles[0]))
        data = colspan_data(data, datacol)
    if font == True:
        data = font_data(data, [])
    html = get_html_data(data, titles, head, widths)
    return html

def get_example_data(dataF):
    head = '表格标题'
    titles = [['大表头1', '大表头1', '大表头2', '大表头3', '大表头3']]
    data = [
        ['小学', '语文', 1, 1, 3],
        ['小学', '数学', 1, 5, 1],
        ['小学', '语文', 1, 1, 33],
        ['初中', '数学', 13, 1, 15],
        ['高中', '数学', 1, 1, 1],
        ['小学', '英语', 1, 8, 1],
        ['小学汇总', '小学汇总', 1, 1, 1],
        ['初中', '数学', 13, 1, 15],
        ['小学', '语文', 1, 1, 33],
        ['高中汇总', '高中汇总', 13, 1, 15]
    ]
    return head, titles, dataF




def run_example():
    # 排序用的年级学科列表
    sorted_grade = get_sort_list('年级')
    sorted_subject = get_sort_list('学科')

    # 常规表格
    head, titles, data = get_example_data()
    html1 = data_to_html(data, titles, head)

    # 排序
    # 先用第一列按年级排序，再用第二列按学科排序，不需要排序的列可以补空数组[]，前面的列排序优先级高
    head, titles, data = get_example_data()
    data = sort_data(data, [sorted_grade, sorted_subject])
    html2 = data_to_html(data, titles, head)

    # 垂直合并表格
    head, titles, data = get_example_data()
    data = sort_data(data, [sorted_grade, sorted_subject])
    html3 = data_to_html(data, titles, head, 2, True, False)

    # 水平合并表格
    head, titles, data = get_example_data()
    data = sort_data(data, [sorted_grade, sorted_subject])
    html4 = data_to_html(data, titles, head, 2, False, True)

    # 垂直水平合并表格
    head, titles, data = get_example_data()
    data = sort_data(data, [sorted_grade, sorted_subject])
    html5 = data_to_html(data, titles, head, 2, True, True)

    # 垂直水平合并表格并自定义宽度
    head, titles, data = get_example_data()
    data = sort_data(data, [sorted_grade, sorted_subject])
    html6 = data_to_html(data, titles, head, 2, True, True, [160, 120, 0, 50])

    # 自定义颜色字号
    head, titles, data = get_example_data()
    data = sort_data(data, [sorted_grade, sorted_subject])
    html7 = data_to_html(data, titles, head, 2, True, True, [], True)

    # 各种功能都用上试试
    head, titles, data = get_example_data()
    data = sort_data(data, [sorted_grade, sorted_subject])
    html8 = data_to_html(data, titles, head, 2, True, True, [160, 120, 0, 50, 200], True)

    mail_to = '123@qq.com'
    subject = '邮件主题'
    msg_txt = html1 + html2 + html3 + html4 + html5 + html6 + html7 + html8
    # send_mail(mail_to, subject, msg_txt)


# if __name__ == '__main__':
#     run_example()
